/*** 
This do-file produces panels B and C of table 'Consumer Spending on Debit 
and Credit Cards, by Income Quartile and Sector'.
***/

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
project, uses("${root}/code/set_globals.do")
include "${root}/code/set_globals.do"
local category "Spending"

* Create required subfolders
cap mkdir "${root}/results/Spending"
cap mkdir "${root}/results/paper numbers"
cap mkdir "${root}/results/paper numbers/`category'"

*-------------------------------------------------------------------------------
* Load daily credit card spending in Jan 2020 
*-------------------------------------------------------------------------------

* Load annualized credit card spending in January 2020
project, uses("${root}/data/derived/NIPA/NIPA Table 2.3.5M Personal Consumption Expenditures by Major Type of Product.dta")
use "${root}/data/derived/NIPA/NIPA Table 2.3.5M Personal Consumption Expenditures by Major Type of Product.dta", clear

* Restrict to spending categories that appear on credit cards
gen in_affinity = 0
replace in_affinity = 1 if serieslabel == "Furnishings and durable household equipment"
replace in_affinity = 1 if serieslabel == "Recreational goods and vehicles"   
replace in_affinity = 1 if serieslabel == "Other durable goods"
replace in_affinity = 1 if serieslabel == "Food and beverages purchased for off-premises consumption"
replace in_affinity = 1 if serieslabel == "Clothing and footwear"
replace in_affinity = 1 if serieslabel == "Gasoline and other energy goods"
replace in_affinity = 1 if serieslabel == "Other nondurable goods"
replace in_affinity = 1 if serieslabel == "Transportation services"
replace in_affinity = 1 if serieslabel == "Recreation services"
replace in_affinity = 1 if serieslabel == "Food services and accommodations"
replace in_affinity = 1 if serieslabel == "Financial services and insurance"
replace in_affinity = 1 if serieslabel == "Other services"
keep if in_affinity == 1

* Collapse all categories
gisid seriescode year month
gcollapse (sum) value, by(year month)
label var value "Nominal Annualized Card Spending, in Millions"

* Output
sort year month
gisid year month

keep if year == 2020 & month == 1
assert _N == 1

* Calculate daily credit card spending in January 2020
scalar nipa_daily_spend = value[1] / 365 * 1e6  // converting annualized spending in millions to daily spending in dollars
di "Daily spending in January 2020: " (nipa_daily_spend)

*-------------------------------------------------------------------------------
* Import Industry shares
*-------------------------------------------------------------------------------

project, uses("${root}/data/web/data/Affinity Industry Composition - National - 2020.csv")
import delimited "${root}/data/web/data/Affinity Industry Composition - National - 2020.csv", clear

rename (share_jan2020 industry) (share sector)

replace sector = strlower(sector)
keep sector share

tempfile ind_shares
save `ind_shares'

*-------------------------------------------------------------------------------
* Prepare data for table
*-------------------------------------------------------------------------------

* Load posted Affinity data
project, uses("${root}/data/web/data/Affinity - National - Daily.csv")
import delim using "${root}/data/web/data/Affinity - National - Daily.csv", asdouble clear

keep year-day spend_all-spend_retail_no_grocery

gen date = mdy(month, day, year)
format date %td
order date

* Reshape long
reshape long spend_, i(date) j(sector) string
replace sector = "all" if sector == "0"
isid date sector

* Keep values from 2020 onward
keep if inrange(date, mdy(1,1,2020), mdy(12,31,2021))

* Merge sector shares
merge m:1 sector using `ind_shares', assert(1 3) keep(3)
sort date sector

* Change in spending since Jan 2020
gen change_since_jan_2020 = spend_ * share * nipa_daily_spend / 1E9


* Calculate each sector's share of aggregate decline
gegen change_sum = sum(change_since_jan_2020), by(date)
gegen change_sum_inperson = sum(change_since_jan_2020) if inlist(sector, "acf", "aer", "tws", "hcs", "inpersonmisc"), by(date)
gen share_decline = change_since_jan_2020 / change_sum if inlist(sector, "durables", "nondurables", "remoteservices")
replace share_decline = change_since_jan_2020 / change_sum_inperson if inlist(sector, "acf", "aer", "tws", "hcs", "inpersonmisc")

* Mean in initial period 
gen initial_spending = 1 * share * nipa_daily_spend / 1E9

* Shifted change 
gen rescaled_change_since_jan_2020 = change_since_jan_2020 + initial_spending

*-------------------------------------------------------------------------------
* Get scalars for table   
*-------------------------------------------------------------------------------

* Set dates to show on table
local early_date = mdy(4, 14, 2020)
local middle_date = mdy(8, 14, 2020)
local late_date = mdy(12, 31, 2021) 

gen period = ""
replace period = "early" if date == `early_date'
replace period = "middle" if date == `middle_date'
replace period = "late" if date == `late_date'

keep period sector change_since_jan_2020 share_decline initial_spending share
drop if period == ""

reshape wide change_since_jan_2020 share_decline, i(sector) j(period) string

rename(change_since_jan_2020* share_decline*) (*_level *_share_decline)
rename(initial_spending share)(jan_level jan_share_decline)

reshape long early middle late jan, i(sector) j(est) string

gen order = 0 if est == "_level" & sector == "durables"
replace order = 1 if est == "_share_decline" & sector == "durables"
replace order = 2 if est == "_level" & sector == "nondurables"
replace order = 3 if est == "_share_decline" & sector == "nondurables"
replace order = 4 if est == "_level" & sector == "remoteservices"
replace order = 5 if est == "_share_decline" & sector == "remoteservices"
replace order = 8 if est == "_level" & sector == "acf"
replace order = 9 if est == "_share_decline" & sector == "acf"
replace order = 10 if est == "_level" & sector == "tws"
replace order = 11 if est == "_share_decline" & sector == "tws"
replace order = 12 if est == "_level" & sector == "hcs"
replace order = 13 if est == "_share_decline" & sector == "hcs"
replace order = 14 if est == "_level" & sector == "aer"
replace order = 15 if est == "_share_decline" & sector == "aer"
replace order = 16 if est == "_level" & sector == "inpersonmisc"
replace order = 17 if est == "_share_decline" & sector == "inpersonmisc"

sort order

* In-person sector 
foreach period in early middle late jan {
    gegen total_`period' = sum(`period') if est == "_level"
	sum total_`period' 
	local total_`period' = `r(mean)'
    gegen inperson_`period' = sum(`period') if est == "_level" & inlist(sector, "acf", "tws", "hcs", "inpersonmisc", "aer")
	sum inperson_`period'
	local inperson_`period' = `r(mean)'
}

local new = _N + 1
set obs `new'

replace sector = "inperson" if sector == ""
replace est = "_level" if est == ""
replace early = `inperson_early' if early== .
replace middle = `inperson_middle' if middle == .
replace late = `inperson_late' if late == . 
replace jan = `inperson_jan' if jan == .
replace order = 6 if order == .

local new = _N + 1
set obs `new'

replace sector = "inperson" if sector == ""
replace est = "_share_decline" if est == ""
replace early = `inperson_early'/`total_early' if early== .
replace middle = `inperson_middle'/`total_middle' if middle == .
replace late = `inperson_late'/`total_late' if late == . 
replace jan = `inperson_jan'/`total_jan' if jan == .
replace order = 7 if order == .

* Rescale inperson subsector initial spending shares to be relative to inperson sector
foreach sector in "acf" "tws" "hcs" "inpersonmisc" "aer" {
	sum jan if est == "_level" & sector == "`sector'"
	local level_`sector' = r(mean)
	replace jan = `level_`sector'' / `inperson_jan' if  est == "_share_decline" & sector == "`sector'"
}

sort order

keep sector early middle late jan
order sector early middle late jan

*-------------------------------------------------------------------------------
* Export
*-------------------------------------------------------------------------------

export excel "${root}/results/new_app_table_5_b_c.xlsx", sheet(new_app_table_5_b_c, replace)
project, creates("${root}/results/new_app_table_5_b_c.xlsx")
